package veilingService;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import veilingDomein.Blocked;
import veilingDomein.Krediet;
import veilingDomein.User;

public class UserService implements IUserService {

	DBController db = new DBController();
	public ArrayList<User> users = new ArrayList<User>();

	public UserService() {

	}

	public void init() {

	}

	@Override
	public User getUser(String username) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		User s = null;
		Krediet k = new Krediet();

		try {
			stmt = con.createStatement();
			rs = stmt
					.executeQuery("SELECT * FROM GEBRUIKER WHERE gebruikersnaam = '"
							+ username + "'");
			while (rs.next()) {
				int id = rs.getInt("gebruiker_id");
				String usern = rs.getString("gebruikersnaam");
				String password = rs.getString("wachtwoord");
				String naam = rs.getString("naam");
				String adres = rs.getString("adres");
				String woonplaats = rs.getString("woonplaats");
				String email = rs.getString("email");
				int kredietid = rs.getInt("KREDIET_ID");
				int rechten = rs.getInt("RECHTEN_ID");
				Statement stmt2 = con.createStatement();
				ResultSet rs2 = stmt2
						.executeQuery("SELECT * FROM KREDIET WHERE KREDIET_ID = "
								+ kredietid + "");
				while (rs2.next()) {
					k.setAantal(rs2.getInt("AANTAL"));
				}
				try {
					s = new User(naam, adres, woonplaats, usern, password,
							email, rechten, id);
					s.setId(id);
					s.setRechten(rechten);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			con.close();
		} catch (SQLException e) {
			System.out.println("SQLException caught: " + e.getMessage());
		}
		// System.out.println(s.getUsername() + " " + s.getKrediet().getAantal()
		// );
		db.closeConnection();
		return s;
	}

	@Override
	public Krediet getKrediet(User user) {
		Krediet k = new Krediet();
		Connection con = db.connect();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT * FROM KREDIET WHERE KREDIET_ID = (SELECT krediet_id FROM GEBRUIKER WHERE GEBRUIKER_ID = "
							+ user.getId() + ")");
			while (rs.next()) {
				k.setAantal(rs.getInt("AANTAL"));
			}
		} catch (SQLException e) {
			System.out.println("SQL Exception: " + e);
		}
		db.closeConnection();
		return k;
	}

	@Override
	public void updateKrediet(Krediet k, User s) {
		Connection con = db.connect();
		try {
			Statement stmt = con.createStatement();

			stmt.executeQuery("update krediet set aantal = "
					+ k.getAantal()
					+ " Where krediet_id = (SELECT krediet_id FROM GEBRUIKER WHERE GEBRUIKER_ID = "
					+ s.getId() + ")");

		} catch (SQLException e) {
			System.out.println("SQL Exception:2 " + e);
		}
		db.closeConnection();
	}

	@Override
	public void voegKredietToe(User user, int aantal) {
		Connection con = db.connect();
		aantal += getUser(user.getUsername()).getKrediet().getAantal();
		try {
			Statement stmt = con.createStatement();
			stmt.executeQuery("UPDATE KREDIET SET AANTAL = '" + aantal
					+ "' WHERE KREDIET_ID = " + user.getId() + "");
		} catch (SQLException e) {
			System.out.println("SQL Exception: " + e);
		}
		db.closeConnection();
	}

	@Override
	public User getUserByID(int id) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		User s = new User();
		try {
			stmt = con.createStatement();
			rs = stmt
					.executeQuery("SELECT * FROM GEBRUIKER WHERE GEBRUIKER_ID = '"
							+ id + "'");
			while (rs.next()) {
				String usern = rs.getString("gebruikersnaam");
				String password = rs.getString("wachtwoord");
				String naam = rs.getString("naam");
				String adres = rs.getString("adres");
				String woonplaats = rs.getString("woonplaats");
				String email = rs.getString("email");
				int rechtenId = rs.getInt("rechten_id");
				try {
					s = new User(naam, adres, woonplaats, usern, password,
							email, rechtenId, id);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			con.close();
		} catch (SQLException e) {
			System.out.println("SQLException caught: " + e.getMessage());
		}
		db.closeConnection();
		return s;
	}

	@Override
	public void createUser(User user) throws Exception {
		Connection con = db.connect();
		String prepquery = "INSERT INTO GEBRUIKER VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String kredietquery = "INSERT INTO KREDIET VALUES(?, ?)";
		try {
			if (con != null) {

				PreparedStatement ps = con.prepareStatement(kredietquery);
				ps.setLong(1, getUserId());
				ps.setInt(2, 100);
				ps.executeUpdate();
				ps.close();

				ps = con.prepareStatement(prepquery);
				ps.setLong(1, getUserId());
				ps.setLong(2, getUserId());
				ps.setLong(3, 1);
				ps.setString(4, user.getNaam());
				ps.setString(5, user.getAdres());
				ps.setString(6, user.getWoonplaats());
				ps.setString(7, user.getUsername());
				ps.setString(8, user.getPassword());
				ps.setString(9, user.getEmail());
				ps.executeUpdate();
				ps.close();
				System.out.println("executeQuery uitgevoerd!");
			} else {
				System.out.println("connection is null");
			}
		} catch (java.sql.SQLException e) {
			System.err.println("SQL_EXCEPTION" + e);
			System.out.println("een sql exeption occurd by executeQuery...");
		}
		db.closeConnection();

	}

	@Override
	public int getNewBlokkeerId() {
		Connection con = db.connect();
		int id = 0;
		try {
			Statement stmt2 = con.createStatement();
			ResultSet rs2 = stmt2
					.executeQuery("SELECT count(*) as id FROM BLOKKEER");
			while (rs2.next()) {
				id = rs2.getInt(1);
			}

		} catch (SQLException e) {
			System.out.println(e);
		}
		db.closeConnection();
		return ++id;

	}

	@Override
	public ArrayList<User> getUsers() {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		User s = new User();
		Krediet k = new Krediet();
		ArrayList<User> users = new ArrayList<User>();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM GEBRUIKER");
			while (rs.next()) {
				int id = rs.getInt("gebruiker_id");
				String usern = rs.getString("gebruikersnaam");
				String password = rs.getString("wachtwoord");
				String naam = rs.getString("naam");
				String adres = rs.getString("adres");
				String woonplaats = rs.getString("woonplaats");
				String email = rs.getString("email");
				int kredietid = rs.getInt("KREDIET_ID");
				int rechten = rs.getInt("RECHTEN_ID");
				Statement stmt2 = con.createStatement();
				ResultSet rs2 = stmt2
						.executeQuery("SELECT * FROM KREDIET WHERE KREDIET_ID = "
								+ kredietid + "");
				while (rs2.next()) {
					k.setAantal(rs2.getInt("AANTAL"));
				}
				try {
					s = new User(naam, adres, woonplaats, usern, password,
							email, rechten, id);
					s.setId(id);
					s.setRechten(rechten);
					users.add(s);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			con.close();
		} catch (SQLException e) {
			System.out.println("SQLException caught: " + e.getMessage());
		}
		db.closeConnection();
		return users;
	}

	@Override
	public void BlokkeerUser(User user, String totDatum, String reden) {
		Connection con = db.connect();
		String sql = "insert into BLOKKEER values(?,?,?,?,?)";
		DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
		java.sql.Date sqldate = null;
		try {
			sqldate = new java.sql.Date(df.parse(totDatum).getTime());
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setLong(1, getNewBlokkeerId());
			pst.setLong(2, user.getId());
			pst.setString(3, reden);
			pst.setDate(4, new java.sql.Date(new java.util.Date().getTime()));
			pst.setDate(5, sqldate);
			pst.executeUpdate();
			pst.close();
			con.close();
		} catch (SQLException e) {
			System.out.println(e);
		}
		db.closeConnection();
	}

	@SuppressWarnings("deprecation")
	@Override
	public Blocked isBlocked(String username) {
		Blocked block = null;
		User s = getUser(username);
		String sql = "Select BLOKKEER_ID, GEBRUIKER_ID, REDEN, TO_CHAR(STARTDATUM, 'YYYY/MM/DD'), TO_CHAR(EINDDATUM, 'YYYY/MM/DD') FROM BLOKKEER WHERE GEBRUIKER_ID = "
				+ s.getId() + " order by einddatum ";
		Connection con = db.connect();
		try {
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery(sql);
			while (res.next()) {
				block = new Blocked(s, new Date(res.getString(4)), new Date(
						res.getString(5)), res.getString(3));
				System.out.println("test: " + block.getUser().getToString()
						+ " einddatum:  " + block.getEind());
			}
			System.out.println("Resultset stopt hier");
		} catch (SQLException e) {
			System.out.println("SQL EXCEPTION: " + e);
		}
		db.closeConnection();
		if (block != null) {
			if (block.getEind().compareTo(new Date()) < 0) {
				block = null;
			}
		}
		db.closeConnection();
		return block;
	}

	@Override
	public int getAantalGebruikers() {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		int aantal = 0;

		try {
			String query = "SELECT count(*) as aantal FROM gebruiker";
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				aantal = rs.getInt("AANTAL");
			}
			db.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return aantal;
	}

	@Override
	public int getUserId() {
		Connection con = db.connect();
		int id = 0;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT max(gebruiker_id) as id FROM gebruiker");
			while (rs.next()) {
				id = rs.getInt(1);
			}

		} catch (SQLException e) {
			System.out.println(e);
		}
		db.closeConnection();
		return ++id;
	}

	@Override
	public void updateUser(User user) {
		Connection con = db.connect();
		System.out.println("connection");
		String prepquery = "Update GEBRUIKER set \"RECHTEN_ID\" = ?, \"GEBRUIKERSNAAM\" = ?, \"WACHTWOORD\" = ?, \"EMAIL\" = ?, \"NAAM\" = ?, \"ADRES\" = ?, \"WOONPLAATS\" = ? where \"GEBRUIKER_ID\" = (?)";
		System.out.println("1: " + prepquery);
		System.out.println("User id: " + user.getId());
		try {
			if (con != null) {

				PreparedStatement ps = con.prepareStatement(prepquery);
				ps.setLong(1, 1);
				ps.setString(2, user.getUsername());
				ps.setString(3, user.getPassword());
				ps.setString(4, user.getEmail());
				ps.setString(5, user.getNaam());
				ps.setString(6, user.getAdres());
				ps.setString(7, user.getWoonplaats());
				ps.setLong(8, user.getId());
				ps.executeUpdate();
				ps.close();
				System.out.println("executeQuery uitgevoerd!");
			} else {
				System.out.println("connection is null");
			}
		} catch (java.sql.SQLException e) {
			System.err.println("SQL_EXCEPTION" + e);
			System.out.println("een sql exeption occurd by executeQuery...");
		}
		db.closeConnection();

	}

	@Override
	public int getAantalGeblokeerdeGebruikers() {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		int aantal = 0;

		try {
			String query = "SELECT count(*) as aantal FROM blokkeer where einddatum > SYSDATE";
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				aantal = rs.getInt("AANTAL");
			}
			db.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return aantal;
	}

	@Override
	public int getAantalGekochteCredits(int userid) {
		Connection con = db.connect();
		Statement stmt = null;
		ResultSet rs = null;
		int aantal = 0;

		try {
			String query = "SELECT sum(aantal) as aantal from kredietfactuur where gebruiker_id = "
					+ userid;
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				aantal = rs.getInt("aantal");
			}
			db.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return aantal;
	}
}
